<?php
/**
 * 数据库管理控制器
 */

declare (strict_types = 1);

namespace app\controller\home;

use app\BaseController;
use think\facade\Db;
use think\facade\Request;
use think\App;

class Database extends BaseController
{
    // 备份文件存储路径
    private $backupPath;
    
    /**
     * 无需登录的方法
     * @var array
     */
    protected $noNeedLogin = ['database', 'list', 'backuplist', 'import', 'downfile', 'del'];
    
    public function __construct(App $app)
    {
        parent::__construct($app);
        
        // 设置备份目录为绝对路径
        $this->backupPath = root_path() . 'data' . DIRECTORY_SEPARATOR . 'backup' . DIRECTORY_SEPARATOR;
        
        // 确保备份目录存在
        if (!is_dir($this->backupPath)) {
            mkdir($this->backupPath, 0755, true);
        }
    }
    
    /**
     * 获取数据表列表
     */
    public function database()
    {
        try {
            // 获取所有表信息
            $tables = Db::query("SHOW TABLE STATUS");
            
            // 计算总大小
            $total = 0;
            $list = [];
            
            foreach ($tables as $table) {
                $item = [
                    'name' => $table['Name'],
                    'engine' => $table['Engine'],
                    'version' => $table['Version'],
                    'row_format' => $table['Row_format'],
                    'rows' => $table['Rows'],
                    'data_length' => $table['Data_length'],
                    'index_length' => $table['Index_length'],
                    'data_free' => $table['Data_free'],
                    'data_size' => $this->formatBytes((int)$table['Data_length']),
                    'create_time' => $table['Create_time'],
                    'comment' => $table['Comment']
                ];
                
                $total += (int)$table['Data_length'];
                $list[] = $item;
            }
            
            // 提示信息
            $dataTips = '数据库中共有<strong> ' . count($list) . '</strong> 张表，共计 <strong>' . $this->formatBytes($total) . '</strong>大小。';
            
            return json([
                'code' => 0,
                'msg' => $dataTips,
                'data' => $list
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '查询失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 查看单个数据表信息
     */
    public function view()
    {
        try {
            $param = Request::param();
            
            if (empty($param['name'])) {
                return json(['code' => 400, 'msg' => '参数错误']);
            }

            // 验证表名格式，防止SQL注入
            $tableName = $param['name'];
            if (!preg_match('/^[a-zA-Z_][a-zA-Z0-9_]*$/', $tableName)) {
                return json(['code' => 400, 'msg' => '无效的表名']);
            }

            // 获取指定表的信息
            $tableInfo = Db::query("SHOW TABLE STATUS LIKE ?", [$tableName]);
            
            if (empty($tableInfo)) {
                return json(['code' => 404, 'msg' => '数据表不存在']);
            }
            
            $table = $tableInfo[0];
            $item = [
                'name' => $table['Name'],
                'engine' => $table['Engine'],
                'version' => $table['Version'],
                'row_format' => $table['Row_format'],
                'rows' => $table['Rows'],
                'data_length' => $table['Data_length'],
                'index_length' => $table['Index_length'],
                'data_free' => $table['Data_free'],
                'data_size' => $this->formatBytes((int)$table['Data_length']),
                'index_size' => $this->formatBytes((int)$table['Index_length']),
                'create_time' => $table['Create_time'],
                'update_time' => $table['Update_time'],
                'check_time' => $table['Check_time'],
                'collation' => $table['Collation'],
                'comment' => $table['Comment']
            ];
            
            return json([
                'code' => 0,
                'msg' => 'success',
                'data' => $item
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '查询失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 备份数据库
     */
    public function backup()
    {
        try {
            $param = Request::param();
            
            // 获取所有表
            $tables = Db::query("SHOW TABLES");
            $tableNames = [];
            foreach ($tables as $table) {
                $tableNames[] = current($table);
            }
            
            // 如果指定了表，则只备份指定的表
            if (!empty($param['tables'])) {
                $selectedTables = is_array($param['tables']) ? $param['tables'] : [$param['tables']];
                $tableNames = array_intersect($tableNames, $selectedTables);
            }
            
            if (empty($tableNames)) {
                return json(['code' => 400, 'msg' => '没有可备份的表']);
            }
            
            // 生成备份文件名
            $filename = 'backup_' . date('YmdHis') . '_' . uniqid() . '.sql';
            $filepath = $this->backupPath . $filename;
            
            // 创建备份文件
            $file = fopen($filepath, 'w');
            if (!$file) {
                return json(['code' => 500, 'msg' => '无法创建备份文件']);
            }
            
            // 写入头部信息
            fwrite($file, "-- 勾股OA数据库备份\n");
            fwrite($file, "-- 生成时间: " . date('Y-m-d H:i:s') . "\n");
            fwrite($file, "-- MySQL版本: " . Db::query("SELECT VERSION() as version")[0]['version'] . "\n\n");
            
            // 备份每个表
            foreach ($tableNames as $tableName) {
                $this->backupTable($file, $tableName);
            }
            
            fclose($file);
            
            return json([
                'code' => 0,
                'msg' => '备份成功',
                'data' => [
                    'filename' => $filename,
                    'filepath' => $filepath,
                    'size' => $this->formatBytes(filesize($filepath))
                ]
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '备份失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 备份单个表
     */
    private function backupTable($file, $tableName)
    {
        // 写入表结构
        fwrite($file, "--\n-- 表结构: {$tableName}\n--\n\n");
        fwrite($file, "DROP TABLE IF EXISTS `{$tableName}`;\n");
        
        $createTable = Db::query("SHOW CREATE TABLE `{$tableName}`")[0]['Create Table'];
        fwrite($file, $createTable . ";\n\n");
        
        // 写入表数据
        fwrite($file, "--\n-- 表数据: {$tableName}\n--\n\n");
        
        $rows = Db::table($tableName)->select();
        if (!empty($rows)) {
            foreach ($rows as $row) {
                $sql = "INSERT INTO `{$tableName}` VALUES (";
                $values = [];
                foreach ($row as $value) {
                    $values[] = is_null($value) ? 'NULL' : "'" . addslashes(strval($value)) . "'";
                }
                $sql .= implode(',', $values) . ");\n";
                fwrite($file, $sql);
            }
        }
        fwrite($file, "\n");
    }
    
    /**
     * 获取备份文件列表
     */
    public function backupList()
    {
        try {
            $files = [];
            if (is_dir($this->backupPath)) {
                $handle = opendir($this->backupPath);
                if ($handle) {
                    while (($file = readdir($handle)) !== false) {
                        if ($file != '.' && $file != '..' && pathinfo($file, PATHINFO_EXTENSION) == 'sql') {
                            $filepath = $this->backupPath . $file;
                            if (file_exists($filepath)) {
                                $files[] = [
                                    'filename' => $file,
                                    'size' => $this->formatBytes(filesize($filepath)),
                                    'mtime' => date('Y-m-d H:i:s', filemtime($filepath))
                                ];
                            }
                        }
                    }
                    closedir($handle);
                    
                    // 按修改时间倒序排列
                    usort($files, function($a, $b) {
                        return strtotime($b['mtime']) - strtotime($a['mtime']);
                    });
                }
            }
            
            return json([
                'code' => 0,
                'msg' => 'success',
                'data' => $files
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '查询失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 恢复数据库
     */
    public function restore()
    {
        try {
            $param = Request::param();
            
            if (empty($param['filename'])) {
                return json(['code' => 400, 'msg' => '请选择要恢复的备份文件']);
            }
            
            $filepath = $this->backupPath . $param['filename'];
            if (!file_exists($filepath)) {
                return json(['code' => 400, 'msg' => '备份文件不存在']);
            }
            
            // 读取备份文件内容
            $sqlContent = file_get_contents($filepath);
            if (empty($sqlContent)) {
                return json(['code' => 400, 'msg' => '备份文件内容为空']);
            }
            
            // 分割SQL语句并执行
            $sqlStatements = explode(";\n", $sqlContent);
            foreach ($sqlStatements as $sql) {
                $sql = trim($sql);
                if (!empty($sql) && strpos($sql, '--') !== 0) {
                    Db::execute($sql);
                }
            }
            
            return json(['code' => 0, 'msg' => '恢复成功']);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '恢复失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 删除备份文件
     */
    public function del()
    {
        try {
            $param = Request::param();
            
            if (empty($param['filename'])) {
                return json(['code' => 400, 'msg' => '请选择要删除的备份文件']);
            }
            
            $filepath = $this->backupPath . $param['filename'];
            if (!file_exists($filepath)) {
                return json(['code' => 400, 'msg' => '备份文件不存在']);
            }
            
            if (unlink($filepath)) {
                return json(['code' => 0, 'msg' => '删除成功']);
            } else {
                return json(['code' => 500, 'msg' => '删除失败']);
            }
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '删除失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 下载备份文件
     */
    public function downfile()
    {
        try {
            $param = Request::param();
            
            if (empty($param['filename'])) {
                return json(['code' => 400, 'msg' => '请选择要下载的备份文件']);
            }
            
            $filepath = $this->backupPath . $param['filename'];
            if (!file_exists($filepath)) {
                return json(['code' => 400, 'msg' => '备份文件不存在']);
            }
            
            // 设置下载头信息
            header('Content-Type: application/octet-stream');
            header('Content-Disposition: attachment; filename="' . basename($filepath) . '"');
            header('Content-Length: ' . filesize($filepath));
            
            // 输出文件内容
            readfile($filepath);
            exit;
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '下载失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 向后兼容方法：获取数据表列表
     */
    public function list()
    {
        return $this->database();
    }
    

    
    /**
     * 向后兼容方法：导入数据库
     */
    public function import()
    {
        return $this->restore();
    }
    
    /**
     * 优化数据表
     */
    public function optimize()
    {
        try {
            $param = Request::param();
            
            // 获取所有表
            $tables = Db::query("SHOW TABLES");
            $tableNames = [];
            foreach ($tables as $table) {
                $tableNames[] = current($table);
            }
            
            // 如果指定了表，则只优化指定的表
            if (!empty($param['tables'])) {
                $selectedTables = is_array($param['tables']) ? $param['tables'] : [$param['tables']];
                $tableNames = array_intersect($tableNames, $selectedTables);
            }
            
            if (empty($tableNames)) {
                return json(['code' => 400, 'msg' => '没有可优化的表']);
            }
            
            // 增加脚本执行时间限制
            set_time_limit(300); // 设置最大执行时间为5分钟
            
            // 优化每个表
            $optimizedTables = [];
            foreach ($tableNames as $tableName) {
                Db::execute("OPTIMIZE TABLE `{$tableName}`");
                $optimizedTables[] = $tableName;
            }
            
            return json([
                'code' => 0, 
                'msg' => '成功优化 ' . count($optimizedTables) . ' 个数据表',
                'data' => [
                    'optimized_tables' => $optimizedTables,
                    'count' => count($optimizedTables)
                ]
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '优化失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 修复数据表
     */
    public function repair()
    {
        try {
            $param = Request::param();
            
            // 获取所有表
            $tables = Db::query("SHOW TABLES");
            $tableNames = [];
            foreach ($tables as $table) {
                $tableNames[] = current($table);
            }
            
            // 如果指定了表，则只修复指定的表
            if (!empty($param['tables'])) {
                $selectedTables = is_array($param['tables']) ? $param['tables'] : [$param['tables']];
                $tableNames = array_intersect($tableNames, $selectedTables);
            }
            
            if (empty($tableNames)) {
                return json(['code' => 400, 'msg' => '没有可修复的表']);
            }
            
            // 增加脚本执行时间限制
            set_time_limit(300); // 设置最大执行时间为5分钟
            
            // 修复每个表
            $repairedTables = [];
            foreach ($tableNames as $tableName) {
                Db::execute("REPAIR TABLE `{$tableName}`");
                $repairedTables[] = $tableName;
            }
            
            return json([
                'code' => 0, 
                'msg' => '成功修复 ' . count($repairedTables) . ' 个数据表',
                'data' => [
                    'repaired_tables' => $repairedTables,
                    'count' => count($repairedTables)
                ]
            ]);
        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '修复失败: ' . $e->getMessage()]);
        }
    }
    
    /**
     * 格式化字节大小
     */
    private function formatBytes($size, $precision = 2)
    {
        if ($size <= 0) {
            return '0 B';
        }
        
        $units = ['B', 'KB', 'MB', 'GB', 'TB'];
        $base = log($size) / log(1024);
        $floor = floor($base);
        $pow = pow(1024, $floor);
        $fixed = round($size / $pow, $precision);
        
        return $fixed . ' ' . $units[$floor];
    }
}